CREATE TABLE [dbo].[SystemUser](
	[SystemUserId] [int] IDENTITY(1,1) NOT NULL,
	[Login] [varchar](50) NOT NULL,
	[Email] [varchar](50) NOT NULL,
	[Phone] [varchar](50) NOT NULL,
	[Role] [varchar](10) NOT NULL,
	[IsActive] [bit] NOT NULL,
	[Password] [varchar](50) NOT NULL,
	[Deleted] [bit] NOT NULL
 CONSTRAINT [PK_SystemUser] PRIMARY KEY CLUSTERED 
(
	[SystemUserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SystemUser] ADD  CONSTRAINT [DF_SystemSystemUser_Email]  DEFAULT ('') FOR [Email]
GO
ALTER TABLE [dbo].[SystemUser] ADD  CONSTRAINT [DF_SystemSystemUser_Phone]  DEFAULT ('') FOR [Phone]
GO
ALTER TABLE [dbo].[SystemUser] ADD  CONSTRAINT [DF_SystemSystemUser_IsActive]  DEFAULT ((1)) FOR [IsActive]
GO
ALTER TABLE [dbo].[SystemUser] ADD  CONSTRAINT [DF_SystemSystemUser_Deleted]  DEFAULT ((0)) FOR [Deleted]
GO
CREATE PROCEDURE [dbo].[spSystemUser_Update]
	@SystemUserId int,
	@email [varchar](50),
	@phone [varchar](50),
	@role [varchar](50),
	@isActive bit
AS
BEGIN
	SET NOCOUNT ON

	update [SystemUser]
	set
		Email = @email
		,Phone = @phone
		,[Role] = @role
		,IsActive = @isActive
	where SystemUserId = @SystemUserId
END
GO
CREATE PROCEDURE [dbo].[spSystemUser_GetList]
AS
BEGIN
	SET NOCOUNT ON

	SELECT *
	from [SystemUser] (nolock)
	where Deleted = 0
END
GO
CREATE PROCEDURE [dbo].[spSystemUser_GetById]
	@SystemUserId int
AS
BEGIN
	SET NOCOUNT ON

	SELECT *
	from [SystemUser] (nolock)
	where SystemUserId = @SystemUserId
END
GO
CREATE PROCEDURE [dbo].[spSystemUser_Delete]
	@SystemUserId int
AS
BEGIN
	SET NOCOUNT ON

	update [SystemUser]
	set Deleted = 1
	where SystemUserId = @SystemUserId
END
GO
CREATE PROCEDURE [dbo].[spSystemUser_Deactivate]
	@SystemUserId int
AS
BEGIN
	SET NOCOUNT ON

	update [SystemUser]
	set IsActive = 0
	where SystemUserId = @SystemUserId
END
GO
CREATE PROCEDURE [dbo].[spSystemUser_ChangeStatus]
	@SystemUserId int,
	@isActive bit
AS
BEGIN
	SET NOCOUNT ON

	update [SystemUser]
	set IsActive = @isActive
	where SystemUserId = @SystemUserId
END
GO
CREATE PROCEDURE [dbo].[spSystemUser_ChangePassword]
	@SystemUserId int,
	@password [varchar](50)
AS
BEGIN
	SET NOCOUNT ON

	update [SystemUser]
	set [Password] = @password
	where SystemUserId = @SystemUserId
END
GO
CREATE PROCEDURE [dbo].[spSystemUser_Add]
	@login [varchar](50),
	@email [varchar](50),
	@phone [varchar](50),
	@password [varchar](50),
	@role [varchar](10)
AS
BEGIN
	SET NOCOUNT ON

	INSERT INTO [SystemUser]
           ([Login]
           ,[Email]
           ,[Phone]
           ,[Password]
           ,[Role])
     VALUES
           (@login
           ,@email
           ,@phone
           ,@password
           ,@role)
END
GO
CREATE PROCEDURE [dbo].[spSystemUser_Activate]
	@SystemUserId int
AS
BEGIN
	SET NOCOUNT ON

	update [SystemUser]
	set IsActive = 1
	where SystemUserId = @SystemUserId
END
GO

